unit RelCategorias;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, cxLookAndFeelPainters, StdCtrls, cxButtons, cxDropDownEdit,
  cxLookupEdit, cxDBLookupEdit, cxDBLookupComboBox, cxLabel, cxTextEdit,
  cxMaskEdit, cxCalendar, cxControls, cxContainer, cxEdit, cxGroupBox,
  cxGraphics, cxCustomData, cxStyles, cxTL, FMTBcd, SqlExpr, Provider, DB,
  DBClient, cxInplaceContainer, cxDBTL, cxTLData, cxCurrencyEdit;

type
  TfrmRelCategorias = class(TForm)
    cxGroupBox2: TcxGroupBox;
    cxGroupBox1: TcxGroupBox;
    deFim: TcxDateEdit;
    deIni: TcxDateEdit;
    cxLabel1: TcxLabel;
    cxGroupBox5: TcxGroupBox;
    cbPessoa: TcxLookupComboBox;
    cxGroupBox3: TcxGroupBox;
    cbOperacao: TcxLookupComboBox;
    cxGroupBox6: TcxGroupBox;
    tlFluxo: TcxDBTreeList;
    tlFluxocxDBTreeListColumn2: TcxDBTreeListColumn;
    tlFluxocxDBTreeListColumn3: TcxDBTreeListColumn;
    dsOperacoes: TDataSource;
    cdsOperacoes: TClientDataSet;
    cdsOperacoesCodigo: TIntegerField;
    cdsOperacoesDescricao: TStringField;
    dspOperacoes: TDataSetProvider;
    sqlOperacoes: TSQLDataSet;
    sqlOperacoesCodigo: TIntegerField;
    sqlOperacoesDescricao: TStringField;
    sqlPessoas: TSQLDataSet;
    sqlPessoasCodigo: TIntegerField;
    sqlPessoasNome: TStringField;
    dspPessoas: TDataSetProvider;
    cdsPessoas: TClientDataSet;
    cdsPessoasCodigo: TIntegerField;
    cdsPessoasNome: TStringField;
    dsPessoas: TDataSource;
    sqlRel: TSQLDataSet;
    dspRel: TDataSetProvider;
    cdsRel: TClientDataSet;
    dsRel: TDataSource;
    sqlRelTIpo: TStringField;
    sqlRelMae: TStringField;
    sqlRelsequencial: TIntegerField;
    sqlRelCodigo: TStringField;
    sqlRelDescricao: TStringField;
    sqlRelValor: TFMTBCDField;
    cdsRelTIpo: TStringField;
    cdsRelMae: TStringField;
    cdsRelsequencial: TIntegerField;
    cdsRelCodigo: TStringField;
    cdsRelDescricao: TStringField;
    cdsRelValor: TFMTBCDField;
    sqlRelValorSoma: TFMTBCDField;
    cdsRelValorSoma: TFMTBCDField;
    bbConsulta: TcxButton;
    btnImprimir: TcxButton;
    btnSair: TcxButton;
    procedure AbreDataSets;
    procedure FormShow(Sender: TObject);
    procedure bbConsultaClick(Sender: TObject);
    procedure btnSairClick(Sender: TObject);
    procedure FormKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
  private
    S:TStringList;
  public
    { Public declarations }
  end;

var
  frmRelCategorias: TfrmRelCategorias;

implementation
uses M_Principal;
{$R *.dfm}

{ TfrmRelCategorias }

procedure TfrmRelCategorias.AbreDataSets;
begin
  cdsPessoas.Open;
  cdsOperacoes.Open;
end;

procedure TfrmRelCategorias.FormShow(Sender: TObject);
begin
  S:=TStringList.Create;
  deIni.Date:=Date-30;
  deFim.Date:=Date;  
  AbreDataSets;
end;

procedure TfrmRelCategorias.bbConsultaClick(Sender: TObject);
var sContaAux:String;
begin
  S.Clear;
  S.Add('Select C.TIpo,C.Mae,C.sequencial,C.Codigo,C.Descricao,');
  S.Add('Case When C.Tipo=''C'' Then IsNull(SUM(ID.ValorTotal),0) else IsNull(SUM(ID.ValorTotal),0) *-1 end as Valor,');
  S.Add('Case When C.Tipo=''C'' Then IsNull(SUM(ID.ValorTotal),0) else IsNull(SUM(ID.ValorTotal),0) *-1 end as ValorSoma');
  S.Add('From Categorias C');
  S.Add('Left Join ItensDocumento ID on C.Sequencial = ID.Categoria');
  S.Add('Left Join Documentos D on ID.SeqDoc = D.Sequencial');
  S.Add('And D.data >='+QuotedStr(FormatDateTime('mm/dd/yyyy',deINi.Date)));
  S.Add('And D.data <='+QuotedStr(FormatDateTime('mm/d/yyyy',deFim.Date)));
  if cbPessoa.EditValue > 0 Then
    S.Add('And D.Pessoa = ' + IntToStr(cbPessoa.EditValue));
  if cbOperacao.EditValue > 0 Then
    S.Add('And D.Operacao = ' + IntToStr(cbOperacao.EditValue));
  S.Add('Group By C.Descricao, C.Codigo,C.Sequencial, C.Tipo, C.Mae');
  S.Add('Order by Codigo');
  with cdsRel do begin
    Close;
    CommandText:=S.text;
    Open;
  end;
  with dtmPrincipal.cdsAux do begin
    Close;
    CommandText:=S.text;
    Open;
    if recordCount > 0 then begin
      First;
      while not Eof do begin
        if FieldByName('Mae').asString <> '' then begin
         cdsRel.Locate('Codigo',Trim(FieldByName('Mae').asString),[locaseinsensitive]);
         cdsRel.Edit;
          cdsRel.FieldByName('Valor').asFloat:=cdsRel.FieldByName('Valor').asFloat + FieldByName('Valor').asFloat;
          cdsRel.Post;
        end;
        Next;
      end;
    end;
  end;

end;

procedure TfrmRelCategorias.btnSairClick(Sender: TObject);
begin
  Close;
end;

procedure TfrmRelCategorias.FormKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  If Key = vk_F9 then begin
    btnSairClick(Sender);
  end;
end;

end.
